import com.sun.rowset.CachedRowSetImpl;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.sql.rowset.CachedRowSet;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;

public class ExportCore {
    private String jdbcDriver = "com.mysql.jdbc.Driver";
    private String dbUrl = "jdbc:mysql://localhost:3306/txhims?autoReconnect=true&useSSL=false";
    // 数据库的用户名与密码，需要根据自己的设置
    private String userName = "root";
    private String passWord = "root";

    public ExportCore(){

    }
    public ExportCore(String dbUrl,String userName,String passWord){
        this.dbUrl = dbUrl;
        this.userName = userName;
        this.passWord = passWord;
    }

    public void export(String dbName,String excelPath){
        System.out.println("Start Export Struct!");
        String dbname = dbName;
        File excelFile = new File(excelPath);
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(excelFile);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("tables");
        String sql = "select TABLE_NAME tbname,TABLE_COMMENT tbcomment from INFORMATION_SCHEMA.tables where TABLE_SCHEMA  = '"+dbname+"' and TABLE_COMMENT != '';";
        String colBaseSql = "SELECT \n" +
                " COLUMN_NAME 列名, \n" +
                " COLUMN_TYPE 数据类型, \n" +
                " DATA_TYPE 字段类型, \n" +
                " CHARACTER_MAXIMUM_LENGTH 长度, \n" +
                " IS_NULLABLE 是否为空, \n" +
                " COLUMN_DEFAULT 默认值, \n" +
                " COLUMN_COMMENT 备注 \n" +
                "FROM \n" +
                " INFORMATION_SCHEMA.COLUMNS \n" +
                "where table_schema ='"+dbname+"' ";
        String colSql = null;
        CachedRowSet tableRS = Query(sql);
        String tbname = null;
        String tbcomment = null;
        int starRow = 0;
        try {
            while(tableRS.next()){
                tbname = tableRS.getString("TABLE_NAME");
                tbcomment = tableRS.getString("TABLE_COMMENT");
                colSql = (colBaseSql + " and table_name = '"+tbname+"'");
                starRow = this.WriteTableStructToExcel(colSql,tbname,tbcomment,sheet,starRow);
            }
            wb.write(fos);
            wb.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }catch (IOException e){
            e.printStackTrace();
        }
    }

    public CachedRowSet Query(String sql){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        CachedRowSet crs = null;
        try{
            Class.forName(jdbcDriver);
            conn = DriverManager.getConnection(dbUrl,userName,passWord);

            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            crs = new CachedRowSetImpl();
            crs.populate(rs);
        }catch(Exception ex){
            ex.printStackTrace();
        }finally {
            try {
                rs.close();
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return crs;
    }

    public int WriteTableStructToExcel(String sql, String tbname, String tbcomment, XSSFSheet sheet, int starRow){
        try {
            CellStyle titleStyle = this.GetTitleStyle(sheet.getWorkbook());
            CellStyle headerStyle = this.GetHeaderStyle(sheet.getWorkbook());
            CellStyle valueStyle = this.GetValueStyle(sheet.getWorkbook());
            CachedRowSet colRS = Query(sql);
            ResultSetMetaData rsmd =  colRS.getMetaData();
            int colCount = rsmd.getColumnCount();
            // 标题
            XSSFRow row = sheet.createRow(starRow);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue(tbname+"("+tbcomment+")");
            cell.setCellStyle(titleStyle);
            sheet.addMergedRegion(new CellRangeAddress(starRow,starRow,0,colCount-1));
            starRow++;
            // 表头
            row = sheet.createRow(starRow);
            for(int i=0;i<colCount;i++){
                cell = row.createCell(i);
                cell.setCellValue(rsmd.getColumnLabel(i+1));
                cell.setCellStyle(headerStyle);
            }
            starRow++;
            while(colRS.next()){
                row = sheet.createRow(starRow);
                for(int i=0;i<colCount;i++){
                    cell = row.createCell(i);
                    cell.setCellValue(colRS.getString(i+1));
                    cell.setCellStyle(valueStyle);
                }
                starRow++;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return starRow+3;
    }

    public CellStyle GetTitleStyle(XSSFWorkbook workbook){
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setBold(true);
        titleStyle.setFont(titleFont);
        return titleStyle;
    }

    public CellStyle GetHeaderStyle(XSSFWorkbook workbook){
        CellStyle headerStyle = workbook.createCellStyle();
        SetBorder(headerStyle);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        return headerStyle;
    }

    public CellStyle GetValueStyle(XSSFWorkbook workbook){
        CellStyle valueStyle = workbook.createCellStyle();
        SetBorder(valueStyle);
        valueStyle.setAlignment(HorizontalAlignment.CENTER);
        valueStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return valueStyle;
    }

    public void SetBorder(CellStyle cellStyle){
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
    };
}
